package br.com.egg.finances.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import br.com.egg.finances.business.dao.util.BDUtils;
import br.com.egg.finances.business.exception.BusinessErrorException;
import br.com.egg.finances.model.Categoria;

public class CategoriaDAO extends DefaultDAO<Categoria> {

	@Override
	public void insert(Categoria entity) {
		Connection connection = null;
		CallableStatement callableStatement = null;
		try {
			StringBuilder sbCall = new StringBuilder();
			sbCall.append("{ CALL inserir_categoria(?, ?, ?, ?) }");
			
			String call = sbCall.toString();
			System.out.println(call);
			
			connection = BDUtils.getConnection();
			callableStatement = connection.prepareCall(call);
			callableStatement.setString(1, entity.getCdUsuario());
			callableStatement.setString(2, entity.getCdCategoriaPai());
			callableStatement.setString(3, entity.getNmCategoria());
			callableStatement.registerOutParameter(4, Types.VARCHAR);
			
			callableStatement.execute();
			
			validaChamdaProcedure(callableStatement, 4);
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, callableStatement, null);
		}
	}

	@Override
	public void update(Categoria entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbUpdate = new StringBuilder();
			sbUpdate.append("UPDATE DEGGCATEGORIA CATEGORIA ");
			sbUpdate.append("SET CATEGORIA.NMCATEGORIA = ? ");
			sbUpdate.append("WHERE CATEGORIA.CDUSUARIO = ? AND CATEGORIA.CDCATEGORIA = ? ");
			
			String update = sbUpdate.toString();
			System.out.println(update);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(update);
			preparedStatement.setString(1, entity.getNmCategoria());
			preparedStatement.setString(2, entity.getCdUsuario());
			preparedStatement.setString(3, entity.getCdCategoria());
			
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public void delete(Categoria entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbDelete = new StringBuilder();
			sbDelete.append("DELETE FROM DEGGCATEGORIA ");
			sbDelete.append("WHERE CDUSUARIO = ? AND CDCATEGORIA = ? ");
			
			String delete = sbDelete.toString();
			System.out.println(delete);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(delete);
			preparedStatement.setString(1, entity.getCdUsuario());
			preparedStatement.setString(2, entity.getCdCategoria());
			
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public Categoria find(Categoria entityFilter) {
		Categoria categoria = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			StringBuilder sbQuery = new StringBuilder();
			sbQuery.append("SELECT CATEGORIA.CDUSUARIO AS CDUSUARIO, CATEGORIA.CDCATEGORIA AS CDCATEGORIA, CATEGORIA.NMCATEGORIA AS NMCATEGORIA, ");
			sbQuery.append("CATEGORIO.CDCATEGORIAPAI AS CDCATEGORIAPAI ");
			sbQuery.append("FROM DEGGCATEGORIA CATEGORIA ");
			sbQuery.append("WHERE CDUSUARIO = ? AND CDCATEGORIA = ? ");
			
			String query = sbQuery.toString();
			System.out.println(query);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, entityFilter.getCdUsuario());
			preparedStatement.setString(2, entityFilter.getCdCategoria());
			
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				categoria = new Categoria();
				categoria.setCdUsuario(resultSet.getString("CDUSUARIO"));
				categoria.setCdCategoria(resultSet.getString("CDCATEGORIA"));
				categoria.setCdCategoriaPai(resultSet.getString("CDCATEGORIAPAI"));
			}
			
			return categoria;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, resultSet);
		}
	}

	@Override
	public List<Categoria> findAll(String cdUsuario) {
		List<Categoria> categoriaList = new ArrayList<Categoria>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			StringBuilder sbQuery = new StringBuilder();
			sbQuery.append("SELECT CATEGORIA.CDUSUARIO AS CDUSUARIO, CATEGORIA.CDCATEGORIA AS CDCATEGORIA, CATEGORIA.NMCATEGORIA AS NMCATEGORIA, ");
			sbQuery.append("CATEGORIA.CDCATEGORIAPAI AS CDCATEGORIAPAI ");
			sbQuery.append("FROM DEGGCATEGORIA CATEGORIA ");
			sbQuery.append("WHERE CATEGORIA.CDUSUARIO = ? ");
			
			String query = sbQuery.toString();
			System.out.println(query);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, cdUsuario);
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Categoria categoria = new Categoria();
				categoria.setCdUsuario(resultSet.getString("CDUSUARIO"));
				categoria.setCdCategoria(resultSet.getString("CDCATEGORIA"));
				categoria.setNmCategoria(resultSet.getString("NMCATEGORIA"));
				categoria.setCdCategoriaPai(resultSet.getString("CDCATEGORIAPAI"));
				
				categoriaList.add(categoria);
			}
			
			return categoriaList;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(null, preparedStatement, resultSet);
		}
	}

}
